﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public class E_DepLevelInfoDal : SqlServerHelper, IDisposable
    {

        public bool AddE_DepLevelInfo(E_DepLevelInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_DepLevelInfo");
            strSql.Append("(");
            strSql.Append("DepId,");
            strSql.Append("LevelId");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@DepId,");
            strSql.Append("@LevelId");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@DepId", SqlDbType.Int, 4) { Value = model.DepId },
                new SqlParameter("@LevelId", SqlDbType.Int, 4) { Value = model.LevelId }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelE_DepLevelInfo(int Id)
        {
            string strSql = "DELETE FROM E_DepLevelInfo WHERE Id=@Id";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = Id }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        /// <summary>
        /// 根据LevelId更新DepId
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool EditE_DepLevelInfo(E_DepLevelInfo model)
        {
            string strSql = "UPDATE dbo.E_DepLevelInfo SET DepId=@DepId WHERE LevelId=@LevelId";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@DepId", SqlDbType.Int, 4) { Value = model.DepId },
                new SqlParameter("@LevelId", SqlDbType.Int, 4) { Value = model.LevelId }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql, pList.ToArray()) > 0;
        }

        public List<E_DepLevelInfo> GetE_DepLevelInfoList(string strWhere)
        {
            List<E_DepLevelInfo> list = new List<E_DepLevelInfo>();
            string strSql = string.Format(@"
SELECT  A.Id ,
        B.Id AS DepId ,
        B.DepName ,
        ISNULL(C.LevelName, '') AS LevelName ,
        ISNULL(C.Id, 0) AS LevelId
FROM    E_DepLevelInfo AS A WITH ( NOLOCK )
        LEFT JOIN E_DepartmentInfo AS B WITH ( NOLOCK ) ON A.DepId = B.Id
                                                           AND B.[Status] = 0
        LEFT JOIN E_LevelInfo AS C WITH ( NOLOCK ) ON A.LevelId = C.Id
                                                      AND C.[Status] = 0
WHERE   1 = 1  {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql))
            {
                int Id = r.GetOrdinal("Id");
                int DepId = r.GetOrdinal("DepId");
                int LevelId = r.GetOrdinal("LevelId");
                int DepName = r.GetOrdinal("DepName");
                int LevelName = r.GetOrdinal("LevelName");
                
                E_DepLevelInfo model = null;
                while (r.Read())
                {
                    model = new E_DepLevelInfo
                    {
                        Id = r.GetInt32(Id),
                        DepId = r.GetInt32(DepId),
                        LevelId = r.GetInt32(LevelId),
                        DepName = r.GetString(DepName),
                        LevelName = r.GetString(LevelName)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<E_DepLevelInfo> GetE_DepLevelInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<E_DepLevelInfo> list = new List<E_DepLevelInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",DepId");
            strSql.Append(",LevelId");
            strSql.AppendFormat(" FROM E_DepLevelInfo WHERE WITH(NOLOCK) 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int DepId = r.GetOrdinal("DepId");
                int LevelId = r.GetOrdinal("LevelId");
                E_DepLevelInfo model = null;
                while (r.Read())
                {
                    model = new E_DepLevelInfo
                    {
                        Id = r.GetInt32(Id),
                        DepId = r.GetInt32(DepId),
                        LevelId = r.GetInt32(LevelId)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            totalCount = GetE_DepLevelInfoCount(strWhere);
            return list;
        }

        public int GetE_DepLevelInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_DepLevelInfo WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public E_DepLevelInfo GetE_DepLevelInfoModel(int id)
        {
            E_DepLevelInfo model = new E_DepLevelInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",DepId");
            strSql.Append(",LevelId");
            strSql.Append(" FROM E_DepLevelInfo WITH(NOLOCK) WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int Id = r.GetOrdinal("Id");
                int DepId = r.GetOrdinal("DepId");
                int LevelId = r.GetOrdinal("LevelId");
                if (r.Read())
                {
                    model.Id = r.GetInt32(Id);
                    model.DepId = r.GetInt32(DepId);
                    model.LevelId = r.GetInt32(LevelId);
                }
                r.Close();
            }
            return model;
        }

    }
}
